﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class AddressGroupDao
    {
        public bool Delete(SqlConnection conn, int groupId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF @UserId IS NULL ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDELETE FROM [dbo].[Address] ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[GroupId] = @GroupId ");
            builder.Append("\t\t\tAND [UserId] IS NULL ");
            builder.Append("\t ");
            builder.Append("\t\tDELETE FROM [dbo].[Address_Group] ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[GroupId] = @GroupId ");
            builder.Append("\t\t\tAND [UserId] IS NULL ");
            builder.Append("\tEND ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDELETE FROM [dbo].[Address] ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[GroupId] = @GroupId ");
            builder.Append("\t\t\tAND [UserId] = @UserId ");
            builder.Append("\t ");
            builder.Append("\t\tDELETE FROM [dbo].[Address_Group] ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[GroupId] = @GroupId ");
            builder.Append("\t\t\tAND [UserId] = @UserId ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@GroupId", SqlDbType.Int);
            commandParameters[0].Value = groupId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (userId == string.Empty)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = userId;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Insert(SqlConnection conn, AddressGroupData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT [dbo].[Address_Group] ");
            builder.Append("( ");
            builder.Append("    [GroupName], ");
            builder.Append("    [UserId] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("    @GroupName, ");
            builder.Append("    @UserId ");
            builder.Append(") ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@GroupName", SqlDbType.NVarChar, 200);
            commandParameters[0].Value = data.GroupName;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (data.UserIdIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.UserId;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public DataTable SelectAll(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE  @TempTable TABLE (ID int identity(2,1), GroupId int ,GroupName nvarchar(200), UserId nvarchar(20)) ");
            builder.Append("IF @UserId IS NULL ");
            builder.Append("\tINSERT INTO @TempTable(GroupId, GroupName, UserId) ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[GroupId], ");
            builder.Append("\t\t[GroupName], ");
            builder.Append("\t\t[UserId]\t ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Address_Group]\t ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[UserId] IS NULL ");
            builder.Append("\tORDER BY ");
            builder.Append("\t\t[GroupId] ASC ");
            builder.Append("ELSE ");
            builder.Append("\tINSERT INTO @TempTable(GroupId, GroupName, UserId) ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[GroupId], ");
            builder.Append("\t\t[GroupName], ");
            builder.Append("\t\t[UserId]\t ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Address_Group]\t ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[UserId]  = @UserId ");
            builder.Append("\tORDER BY ");
            builder.Append("\t\t[GroupId] ASC ");
            builder.Append("SELECT * FROM @TempTable ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            if (userId == string.Empty)
            {
                commandParameters[0].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0].Value = userId;
            }
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Address_Group";
            return set.Tables[0];
        }

        public DataTable SelectByGroupId(SqlConnection conn, int groupId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF @UserId IS NULL ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[GroupId], ");
            builder.Append("\t\t[GroupName], ");
            builder.Append("\t\t[UserId] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Address_Group] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[GroupId] = @GroupId ");
            builder.Append("\t\tAND [UserId] IS NULL ");
            builder.Append("ELSE ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t[GroupId], ");
            builder.Append("\t\t[GroupName], ");
            builder.Append("\t\t[UserId] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Address_Group] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[GroupId] = @GroupId ");
            builder.Append("\t\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@GroupId", SqlDbType.Int);
            commandParameters[0].Value = groupId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (userId == string.Empty)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = userId;
            }
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Address_Group";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, AddressGroupData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Address_Group] ");
            builder.Append("SET ");
            builder.Append("\t[GroupName] = @GroupName, ");
            builder.Append("\t[UserId] = @UserId ");
            builder.Append("WHERE ");
            builder.Append("\t[GroupId] = @GroupId ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@GroupId", SqlDbType.Int);
            commandParameters[0].Value = data.GroupId;
            commandParameters[1] = new SqlParameter("@GroupName", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = data.GroupName;
            commandParameters[2] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            if (data.UserIdIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.UserId;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

